﻿if exists (select * from sysobjects where id = object_id(N'pBUGLOGBugsSummaryUpdate') and sysstat & 0xf = 4) drop procedure pBUGLOGBugsSummaryUpdate 
GO

-- Updates a record in the [dbo].[BugsSummary] table.
-- Concurreny is supported by using checksum method.
CREATE PROCEDURE pBUGLOGBugsSummaryUpdate
    @pk_BugId int,
    @p_BugShortDescription varchar(50),
    @p_Date smalldatetime,
    @p_userID int,
    @p_PriorityID int,
    @p_StatusID int,
    @p_Assigned int,
    @p_FixedBy int,
    @p_Description nvarchar(450),
    @p_EnvBUGLOGment nvarchar(450),
    @p_StepsToReproduce nvarchar(450),
    @p_Note ntext,
    @p_ImageAttachment image,
    @p_TaskID int,
    @p_TestCaseID int,
    @p_prevConValue nvarchar(4000),
    @p_force_update  char(1)
AS
DECLARE
    @l_newValue nvarchar(4000),
    @return_status int,
    @l_rowcount int
BEGIN
-- Check whether the record still exists before doing update
    IF NOT EXISTS (SELECT * FROM [dbo].[BugsSummary] WHERE [BugId] = @pk_BugId)
        RAISERROR ('Concurrency Error: The record has been deleted by another user. Table [dbo].[BugsSummary]', 16, 1)

    -- If user wants to force update to happen even if 
    -- the record has been modified by a concurrent user,
    -- then we do this.
    IF (@p_force_update = 'Y')
        BEGIN

            -- Update the record with the passed parameters
            UPDATE [dbo].[BugsSummary]
            SET 
            [BugShortDescription] = @p_BugShortDescription,
            [Date] = @p_Date,
            [userID] = @p_userID,
            [PriorityID] = @p_PriorityID,
            [StatusID] = @p_StatusID,
            [Assigned] = @p_Assigned,
            [FixedBy] = @p_FixedBy,
            [Description] = @p_Description,
            [EnvBUGLOGment] = @p_EnvBUGLOGment,
            [StepsToReproduce] = @p_StepsToReproduce,
            [Note] = @p_Note,
            [ImageAttachment] = @p_ImageAttachment,
            [TaskID] = @p_TaskID,
            [TestCaseID] = @p_TestCaseID
            WHERE [BugId] = @pk_BugId

            -- Make sure only one record is affected
            SET @l_rowcount = @@ROWCOUNT
            IF @l_rowcount = 0
                RAISERROR ('The record cannot be updated.', 16, 1)
            IF @l_rowcount > 1
                RAISERROR ('duplicate object instances.', 16, 1)

        END
    ELSE
        BEGIN
            -- Get the checksum value for the record 
            -- and put an update lock on the record to 
            -- ensure transactional integrity.  The lock
            -- will be release when the transaction is 
            -- later committed or rolled back.
            Select @l_newValue = CAST(BINARY_CHECKSUM([BugId],[BugShortDescription],[Date],[userID],[PriorityID],[StatusID],[Assigned],[FixedBy],[Description],[EnvBUGLOGment],[StepsToReproduce],[Note],[ImageAttachment],[TaskID],[TestCaseID]) AS nvarchar(4000)) 
            FROM [dbo].[BugsSummary] with (rowlock, holdlock)
            WHERE [BugId] = @pk_BugId


            -- Check concurrency by comparing the checksum values
            IF (@p_prevConValue = @l_newValue)
                SET @return_status = 0     -- pass
            ElSE
                SET @return_status = 1     -- fail

            -- Concurrency check passed.  Go ahead and 
            -- update the record
            IF (@return_status = 0)
                BEGIN

                    UPDATE [dbo].[BugsSummary]
                    SET 
                    [BugShortDescription] = @p_BugShortDescription,
                    [Date] = @p_Date,
                    [userID] = @p_userID,
                    [PriorityID] = @p_PriorityID,
                    [StatusID] = @p_StatusID,
                    [Assigned] = @p_Assigned,
                    [FixedBy] = @p_FixedBy,
                    [Description] = @p_Description,
                    [EnvBUGLOGment] = @p_EnvBUGLOGment,
                    [StepsToReproduce] = @p_StepsToReproduce,
                    [Note] = @p_Note,
                    [ImageAttachment] = @p_ImageAttachment,
                    [TaskID] = @p_TaskID,
                    [TestCaseID] = @p_TestCaseID
                    WHERE [BugId] = @pk_BugId

                    SET @l_rowcount = @@ROWCOUNT
                    IF @l_rowcount = 0
                        RAISERROR ('The record cannot be updated.', 16, 1)
                    IF @l_rowcount > 1
                        RAISERROR ('duplicate object instances.', 16, 1)

                END
            ELSE
            -- Concurrency check failed.  Inform the user by raising the error
                RAISERROR ('Concurrency Error: The record has been updated by another user. Table [dbo].[BugsSummary]', 16, 1)

        END
END

